SQL Stored Procedure

SQL Stored Procedures The powerful feature of SQL databases is that they allow you to embed complex SQL logic and complex business rules in reusable blocks of code. 

Here is a breakdown of what they do and how they work.

What is a Stored Procedure

A stored procedure is a collection of pre-collected SQL statements and information about flow options stored in a database. They can retrieve parameters, perform operations, and return results, making it versatile for different projects.

Key Features of Stored Procedures

Encapsulation- Combine multiple SQL statements into a single object. This encapsulation makes it easier to deal with complex logic.

Reusability- Once defined, the stored procedure can be executed repeatedly without having to rewrite the SQL code.

Performance- Stored routes are collected once and stored in the database, which can improve performance due to reduced search time

Security- Data can be helped to be protected by limiting direct access to the underlying tables. Users can be allowed to execute a stored procedure without accessing the base tables.

Parameterization- Stored objects can accept parameters, allowing dynamic actions to be taken based on input values.

 

Basic Structure of a Stored Procedure

Here is a simple syntax of creating a stored procedure in SQL Server,

USE DatabaseName
GO
CREATE PROCEDURE Pro_ProcedureName
(
@Parameter1 datatype,
@Parameter2 datatype,
........
)AS
BEGIN
SET NOCOUNT ON;
--SQL statement here
SET NOCOUNT OFF;
END

• USE DatabaseName- the SQL database name in which you want to create stored procedure.
• CREATE PROCEDURE/PROC is the SQL statement that is used to create a stored procedure.
• Pro_ProcedureName is the name of stored procedure.
• @Parameter1 datatype and @Parameter2 datatype is the parameters with datatype which are used to accept the values and return the result based on it.
• BEGIN … END is the body area of stored procedure in which SQL statement is placed.
• SET NOCOUNT ON/OFF is used to ignore the affected row that is returned by the SQL statement in the body area.

Example- 

Here are the two SQL tables, Employees with the columns, EmpId, EmpName, Gender, Salary, DepartmentId and Departments table with the columns, ID, Name, Location

Let's create a stored procedure that accepts the department name as a parameter and returns all the employees of that department,

USE MyCollegeDb
GO
CREATE PROCEDURE [dbo].[Pro_GetEmpbyDepartment]
(
@DeptName NVARCHAR(50) -- parameter
)AS
BEGIN
SET NOCOUNT ON;
 -- body area 
 SELECT Emp.EmpId, Emp.EmpName, D.Name AS DepartmentName, Emp.Gender, Emp.Salary
 FROM Employees AS Emp INNER JOIN Departments D ON D.ID = Emp.DepartmentId 
 WHERE D.Name = @DeptName
 -- use for better comparision D.Name LIKE '%'+@DeptName+'%'
SET NOCOUNT OFF;
END

 

Now, execute the stored procedure,

If you notice in the above result table all the employees' data is returned from the 'HR' department.

 

SQL Stored Procedure Without Parameter 

you can also create stored procedure without any parameter or parameterless stored procedure, Let's see the example below,

 

USE MyCollegeDb
GO
CREATE PROCEDURE [dbo].[Pro_GetEmployeeDetails]
AS
BEGIN
SET NOCOUNT ON;
 -- body area 
 SELECT Emp.EmpId, Emp.EmpName, D.Name AS DepartmentName, Emp.Gender, Emp.Salary
 FROM Employees AS Emp INNER JOIN Departments D ON D.ID = Emp.DepartmentId 
 ORDER BY Emp.EmpName DESC
SET NOCOUNT OFF;
END

Output- 

 

Benefits of Using Stored Procedures

Modularity- Break up complex logic into manageable chunks.
Maintainability- Changes can be made in one area without affecting the entire application.
Consistency- Ensures logic is consistently addressed across applications and users.

 

Use Cases for Stored Procedures

Data Manipulation- Inserting, updating, or deleting records based on extreme circumstances.
Batch processing- Executing multiple SQL statements as a batch to improve performance.
Data Validation- Implementation of business rules and verification checks before data manipulation.
Reporting- Gathering and organizing data and producing complex reports.

Stored procedures are an important part of SQL design that provide significant benefits in terms of performance, security, and maintainability. It is widely used in enterprise applications to efficiently manage database interactions.

 

 

Also, Read: Explain the SQL triggers and their uses